﻿using System;
using System.IO;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Resources;

using CE.User;
using CE.Global;
using CE.Database;
using CE.Report;

public partial class telereport_agent_tele_summary : System.Web.UI.Page
{
    string PageCode = "GGRPTTMGGCSUM";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session[CE.Global.General.UserId] == null)
        {
            Response.Redirect("~/ManagementSecureLogin.aspx");
        }

        if (!IsPostBack)
        {
            if (Convert.ToInt64(Session[General.UserId].ToString()) != 1)
            {
                CheckPageAccess();
            }

            lblError.Text = "";
            lblContent.Text = "";
            ddlDay1.Visible = true;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
            ddlYear1.Items.Clear();
            ddlYear2.Items.Clear();

            int _cryr = CE.Global.General.GetUTCDateTime().Year;
            int _styr = 2010;
            for (int _year = _cryr; _year >= _styr; _year--)
            {
                ddlYear1.Items.Add(new ListItem(_year.ToString(), _year.ToString()));
                ddlYear2.Items.Add(new ListItem(_year.ToString(), _year.ToString()));
            }

            ddlDay1.Text = CE.Global.General.GetUTCDateTime().Day.ToString("00");
            ddlMonth1.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
            ddlYear1.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
            ddlDay2.Text = CE.Global.General.GetUTCDateTime().Day.ToString("00");
            ddlMonth2.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
            ddlYear2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
        }
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        lblError.Text = "";
        //lblTotalCallFinishCount.Text = "";
        //divTotalCallFinishTitle.Visible = false;
        txtReportType.Text = ddlReportType.Text;
        txtAgentId.Text = Session[CE.Global.General.UserId].ToString();
        LoadGrid();
    }

    protected void btnDownLoad_Click(object sender, EventArgs e)
    {
        //btnSubmit_Click(sender, e);
        GenerateExcel();
        //GenerateExcelList();
    }

    protected void ddlReportType_SelectedIndexChanged(object sender, EventArgs e)
    {
        lblError.Text = "";
        ddlDay1.Text = CE.Global.General.GetUTCDateTime().Day.ToString("00");
        ddlDay2.Text = CE.Global.General.GetUTCDateTime().Day.ToString("00");
        ddlMonth1.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
        ddlMonth2.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
        ddlYear1.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
        ddlYear2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
        if (Convert.ToInt16(ddlReportType.Text) == 0)
        {
            ddlDay1.Visible = false;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 1)
        {
            ddlDay1.Visible = true;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 2)
        {
            lblFromDate.Text = "From Date :";
            lblToDate.Text = " To Date :";
            lblToDate.Visible = true;
            ddlDay2.Visible = true;
            ddlMonth2.Visible = true;
            ddlYear2.Visible = true;
            ddlDay1.Visible = true;
        }
    }

    protected void grvTeleSummary_SelectedIndexChanged(object sender, EventArgs e)
    {
        btnSubmit_Click(sender, e);
    }

    protected void grvTeleSummary_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //e.Row.Cells[4].Attributes.CssStyle.Add("text-align", "right");
            //Int32 _Count = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "CallFinishCount"));
            //Int32 _TotalCount = Convert.ToInt32(txtHideTotalCallFinishCount.Text);
            //_TotalCount = _TotalCount + _Count;
            //txtHideTotalCallFinishCount.Text = _TotalCount.ToString();
            //lblTotalCallFinishCount.Text = _TotalCount.ToString();
        }
    }

    public bool ValidDate(Int32 Year, Int32 Month, Int32 Day)
    {
        bool _isValid = true;
        int daysInMonth = 0;
        string leap, monthValid, dayValid, yearValid;
        if (Year >= 0000)
        {
            yearValid = "yes";
        }
        else
        {
            yearValid = "no";
        }
        if (Year % 400 == 0)
        {
            leap = "yes";
        }
        else if (Year % 100 == 0)
        {
            leap = "no";
        }
        else if (Year % 4 == 0)
        {
            leap = "yes";
        }
        else
        {
            leap = "no";
        }

        switch (Month)
        {
            case 1: daysInMonth = 31;
                break;
            case 3: daysInMonth = 31;
                break;
            case 4: daysInMonth = 30;
                break;
            case 5: daysInMonth = 31;
                break;
            case 6: daysInMonth = 30;
                break;
            case 7: daysInMonth = 31;
                break;
            case 8: daysInMonth = 31;
                break;
            case 9: daysInMonth = 30;
                break;
            case 10: daysInMonth = 31;
                break;
            case 11: daysInMonth = 30;
                break;
            case 12: daysInMonth = 31;
                break;
        }

        if (Month >= 1 && Month <= 12)
        {
            monthValid = "yes";
        }
        else
        {
            monthValid = "no";
        }
        if (Month == 2)
        {
            if (leap == "no")
            {
                daysInMonth = 28;
            }
            else if (leap == "yes")
            {
                daysInMonth = 29;
            }
        }

        if (1 <= Day && Day <= daysInMonth)
        {
            dayValid = "yes";
        }

        else
        {
            dayValid = "no";
        }

        if ((dayValid == "yes") && (monthValid == "yes") && (yearValid == "yes"))
        {
            _isValid = true;
        }
        else
        {
            _isValid = false;
        }

        return _isValid;
    }

    public string GetMonth(string month)
    {
        string result = "";
        if (month == "01")
        {
            result = " Jan ";
        }
        else if (month == "02")
        {
            result = " Feb ";
        }
        else if (month == "03")
        {
            result = " Mar ";
        }
        else if (month == "04")
        {
            result = " Apr ";
        }
        else if (month == "05")
        {
            result = " May ";
        }
        else if (month == "06")
        {
            result = " Jun ";
        }
        else if (month == "07")
        {
            result = " Jul ";
        }
        else if (month == "08")
        {
            result = " Aug ";
        }
        else if (month == "09")
        {
            result = " Sep ";
        }
        else if (month == "10")
        {
            result = " Oct ";
        }
        else if (month == "11")
        {
            result = " Nov ";
        }
        else if (month == "12")
        {
            result = " Dec ";
        }
        return result;
    }

    public bool Validatioin()
    {
        lblError.Text = "";
        bool _isValid = true;
        DateTime _fromDate, _toDate;
        if (Convert.ToInt16(ddlReportType.Text) == 1)
        {
            if (ddlDay1.SelectedIndex >= 0)
            {
                if (ValidDate(Convert.ToInt32(ddlYear1.Text), Convert.ToInt32(ddlMonth1.Text), Convert.ToInt32(ddlDay1.Text)))
                {
                    _fromDate = Convert.ToDateTime(Convert.ToInt32(ddlYear1.Text) + "/" + Convert.ToInt32(ddlMonth1.Text) + "/" + Convert.ToInt32(ddlDay1.Text));
                    if (_fromDate > CE.Global.General.GetUTCDateTime())
                    {
                        _isValid = false;
                        lblError.Text = "Date should be less than or equal to Today";
                    }
                }
                else
                {
                    _isValid = false;
                    lblError.Text = "Select Valid Date";
                }
            }
            else
            {
                _isValid = false;
                lblError.Text = "Select Date";
            }
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 2)
        {
            if (ddlDay1.SelectedIndex >= 0 && ddlDay2.SelectedIndex >= 0)
            {
                if (ValidDate(Convert.ToInt32(ddlYear1.Text), Convert.ToInt32(ddlMonth1.Text), Convert.ToInt32(ddlDay1.Text)))
                {
                    _fromDate = Convert.ToDateTime(Convert.ToInt32(ddlYear1.Text) + "/" + Convert.ToInt32(ddlMonth1.Text) + "/" + Convert.ToInt32(ddlDay1.Text));
                    if (ValidDate(Convert.ToInt32(ddlYear2.Text), Convert.ToInt32(ddlMonth2.Text), Convert.ToInt32(ddlDay2.Text)))
                    {
                        _toDate = Convert.ToDateTime(Convert.ToInt32(ddlYear2.Text) + "/" + Convert.ToInt32(ddlMonth2.Text) + "/" + Convert.ToInt32(ddlDay2.Text));
                        if (_fromDate <= CE.Global.General.GetUTCDateTime())
                        {
                            if (_fromDate > _toDate)
                            {
                                _isValid = false;
                                lblError.Text = "From date should be less than To date";
                            }
                            else
                            {
                                TimeSpan span = _toDate.Subtract(_fromDate);
                                if (span.Days > 31)
                                {
                                    _isValid = false;
                                    lblError.Text = "Number of Days Should not be Exceed 31 days";
                                }
                            }
                        }
                        else
                        {
                            _isValid = false;
                            lblError.Text = "From date should be less than Today";
                        }
                    }
                    else
                    {
                        _isValid = false;
                        lblError.Text = "Select Valid To date";
                    }
                }
                else
                {
                    _isValid = false;
                    lblError.Text = "Select Valid From date";
                }
            }
            else
            {
                _isValid = false;
                lblError.Text = "Select From date and To date";
            }
        }
        return _isValid;
    }

    public void LoadGrid()
    {
        lblError.Text = "";
        if (Validatioin() == true)
        {
            txtReportDate1.Text = "";
            txtReportDate2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000") + CE.Global.General.GetUTCDateTime().Month.ToString("00") + CE.Global.General.GetUTCDateTime().Day.ToString("00");
            txtReportDate1.Text = ddlYear1.Text;
            txtReportDate1.Text += ddlMonth1.Text;
            txtReportDate1.Text += ddlDay1.Text;
            if (ddlReportType.SelectedIndex == 0)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
            }
            if (ddlReportType.SelectedIndex == 1)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
                txtReportDate1.Text += ddlDay1.Text;
            }
            else if (ddlReportType.SelectedIndex == 2)
            {
                txtReportDate2.Text = ddlYear2.Text;
                txtReportDate2.Text += ddlMonth2.Text;
                txtReportDate2.Text += ddlDay2.Text;
            }
            DataTable _dtSummary = new DataTable();
            Int16 _ReportType = Convert.ToInt16(ddlReportType.Text);
            string _ReportDate1 = txtReportDate1.Text.Trim();
            string _ReportDate2 = txtReportDate2.Text.Trim();

            if (ddlReportType.Text == "0" && _ReportDate1.Length == 6)
            {
                _ReportDate1 += "01";
            }
            Int16 Renewal = Convert.ToInt16(ddlRenewal.Text);

            MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
            string _spName = "[sprocCXCRMReportsBrokersTeleMarketingSelectedItem]";
            SortedList _sPara = new SortedList();

            _sPara.Add("@ReportType", _ReportType);
            _sPara.Add("@ReportDate1", _ReportDate1);
            _sPara.Add("@ReportDate2", _ReportDate2);
            _sPara.Add("@Renewal", Renewal);

            _dtSummary = _db.GetDataTableBySP(_spName, _sPara);
            if (_dtSummary != null && _dtSummary.Rows.Count > 0)
            {
                lblError.Text = "";
                grvTeleSummary.DataSource = _dtSummary;
                grvTeleSummary.DataBind();
                EnablePrintView();

            }
            else
            {
                grvTeleSummary.DataSource = null;
                grvTeleSummary.DataBind();
                EnablePrintView();
            }
        }
        else
        {
            grvTeleSummary.DataSource = null;
            grvTeleSummary.DataBind();
            EnablePrintView();
        }
    }

    private void EnablePrintView()
    {
        if (grvTeleSummary.Rows.Count > 0)
        {
            ddlLanguage.Visible = true;
            btnDownLoad.Visible = true;
            lblRecordCount.Text = "(" + grvTeleSummary.Rows.Count.ToString() + ") record(s). Date/Time: " + CE.Global.General.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt") + "&nbsp;&nbsp;";
        }
        else
        {
            ddlLanguage.Visible = false;
            btnDownLoad.Visible = false;
            //lblRecordCount.Text = "No record found. Date/Time: " + CE.Global.General.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt") + "&nbsp;&nbsp;";
            lblRecordCount.Text = "";
        }
    }

    public void GenerateExcelOld()
    {
        string _html = "";
        string _fileName = "GGCampaign@" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
        lblContent.Text = "";
        lblError.Text = "";
        if (Validatioin() == true)
        {
            txtReportDate1.Text = "";
            txtReportDate2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000") + CE.Global.General.GetUTCDateTime().Month.ToString("00") + CE.Global.General.GetUTCDateTime().Day.ToString("00");
            txtReportDate1.Text = ddlYear1.Text;
            txtReportDate1.Text += ddlMonth1.Text;
            txtReportDate1.Text += ddlDay1.Text;
            if (ddlReportType.SelectedIndex == 0)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
            }
            if (ddlReportType.SelectedIndex == 1)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
                txtReportDate1.Text += ddlDay1.Text;
            }
            if (ddlReportType.SelectedIndex == 2)
            {
                txtReportDate2.Text = ddlYear2.Text;
                txtReportDate2.Text += ddlMonth2.Text;
                txtReportDate2.Text += ddlDay2.Text;
            }
            Int32 TotalFileCount = 0;
            DataTable _dtSummary = new DataTable();
            Int16 _ReportType = Convert.ToInt16(ddlReportType.Text);
            string _ReportDate1 = txtReportDate1.Text.Trim();
            string _ReportDate2 = txtReportDate2.Text.Trim();

            if (ddlReportType.Text == "0" && _ReportDate1.Length == 6)
            {
                _ReportDate1 += "01";
            }
            Int16 Renewal = Convert.ToInt16(ddlRenewal.Text);

            MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
            string _spName = "[sprocCXCRMReportsBrokersTeleMarketingSelectedItem]";
            SortedList _sPara = new SortedList();

            _sPara.Add("@ReportType", _ReportType);
            _sPara.Add("@ReportDate1", _ReportDate1);
            _sPara.Add("@ReportDate2", _ReportDate2);
            _sPara.Add("@Renewal", Renewal);

            _dtSummary = _db.GetDataTableBySP(_spName, _sPara);
            if (_dtSummary != null && _dtSummary.Rows.Count > 0)
            {
                TotalFileCount += _dtSummary.Rows.Count;

                #region Variable Declaration

                string[] BrokerName = new string[TotalFileCount];
                Int32[] VMISales = new Int32[TotalFileCount];
                float[] CollectedPremium = new float[TotalFileCount];
                float[] SalesDIVDay = new float[TotalFileCount];
                Int32[] Calls = new Int32[TotalFileCount];
                float[] CallsDIVDay = new float[TotalFileCount];
                Int32[] NewLeads = new Int32[TotalFileCount];
                float[] NewLeadsDIVDay = new float[TotalFileCount];
                Int32[] InvalidNo = new Int32[TotalFileCount];
                float[] ContactRate = new float[TotalFileCount];
                Int32[] FollowUpCalls = new Int32[TotalFileCount];
                float[] ConversionRate = new float[TotalFileCount];

                Int32 TotalVMISales = 0;
                float TotalCollectedPremium = 0.0f;
                float TotalSalesDIVDay = 0.0f;
                Int32 TotalCalls = 0;
                float TotalCallsDIVDay = 0.0f;
                Int32 TotalNewLeads = 0;
                float TotalNewLeadsDIVDay = 0.0f;
                Int32 TotalInvalidNo = 0;
                float TotalContactRate = 0.0f;
                Int32 TotalFollowUpCalls = 0;
                float TotalConversionRate = 0.0f;
                float AvgTotalCollectedPremium = 0.0f;
                float AvgTotalContactRate = 0.0f;
                Int32 NoofWorkingDays = 0;
                float AvgTotalConversionRate = 0.0f;
                
                #endregion

                #region Value Assign

                for (int i = 0; i < TotalFileCount; i++)
                {
                    BrokerName[i] = _dtSummary.Rows[i]["Name"].ToString();
                    VMISales[i] = Convert.ToInt32(_dtSummary.Rows[i]["VMISales"].ToString());
                    CollectedPremium[i] = Convert.ToSingle(_dtSummary.Rows[i]["CollectedPremium"].ToString());
                    SalesDIVDay[i] = Convert.ToSingle(_dtSummary.Rows[i]["SalesDIVDay"].ToString());
                    Calls[i] = Convert.ToInt32(_dtSummary.Rows[i]["TotalCalls"].ToString());
                    CallsDIVDay[i] = Convert.ToSingle(_dtSummary.Rows[i]["CallsDIVDay"].ToString());
                    NewLeads[i] = Convert.ToInt32(_dtSummary.Rows[i]["NewLeads"].ToString());
                    NewLeadsDIVDay[i] = Convert.ToSingle(_dtSummary.Rows[i]["NewLeadsDIVDay"].ToString());
                    InvalidNo[i] = Convert.ToInt32(_dtSummary.Rows[i]["InvalidNo"].ToString());
                    ContactRate[i] = Convert.ToSingle(_dtSummary.Rows[i]["ContactRate"].ToString());
                    FollowUpCalls[i] = Convert.ToInt32(_dtSummary.Rows[i]["FollowUpCalls"].ToString());
                    NoofWorkingDays = Convert.ToInt32(_dtSummary.Rows[i]["WorkingDays"].ToString());
                    ConversionRate[i] = Convert.ToSingle(_dtSummary.Rows[i]["ConversionRate"].ToString());
                    TotalVMISales += VMISales[i];
                    TotalCollectedPremium += CollectedPremium[i];
                    TotalSalesDIVDay += SalesDIVDay[i];
                    TotalCalls += Calls[i];
                    TotalCallsDIVDay += CallsDIVDay[i];
                    TotalNewLeads += NewLeads[i];
                    TotalNewLeadsDIVDay += NewLeadsDIVDay[i];
                    TotalInvalidNo += InvalidNo[i];
                    TotalContactRate += ContactRate[i];
                    TotalFollowUpCalls += FollowUpCalls[i];
                    TotalConversionRate += ConversionRate[i];

                }

                AvgTotalCollectedPremium = TotalCollectedPremium;
                if (TotalCollectedPremium > 0 && TotalFileCount > 0)
                {
                    AvgTotalCollectedPremium = TotalCollectedPremium / Convert.ToSingle(TotalFileCount);
                }
                AvgTotalContactRate = TotalContactRate;
                if (TotalContactRate > 0 && TotalFileCount > 0)
                {
                    AvgTotalContactRate = TotalContactRate / Convert.ToSingle(TotalFileCount);
                }
                AvgTotalConversionRate = TotalConversionRate;
                if (TotalConversionRate > 0 && TotalFileCount > 0)
                {
                    AvgTotalConversionRate = TotalConversionRate / Convert.ToSingle(TotalFileCount);
                }
                #endregion
                
                #region Generate Excel

                string ReportMonth = "";
                if (_ReportType == 0)
                {
                    ReportMonth = GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                }
                else if (_ReportType == 1)
                {
                    ReportMonth = ddlDay1.Text + " " + GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                }
                else if (_ReportType == 2)
                {
                    ReportMonth = ddlDay1.Text + " " + GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                    ReportMonth += " - ";
                    ReportMonth += ddlDay2.Text + " " + GetMonth(ddlMonth2.Text).Trim() + " " + ddlYear2.Text;
                }
                _html += "<html>";
                _html += "<head>";
                _html += "<meta http-equiv='content-type' content='text/html; charset=UTF-8'>";
                _html += "</head>";
                _html += "<body>";
                _html += "<table width='100%' cellpadding='1' cellspacing='1' border='1'>";
                if (ddlLanguage.Text == "1")
                {
                    _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>กล้วยๆ แคมเปญ " + ReportMonth + "</b></td></tr>";
                    _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>[" + NoofWorkingDays.ToString() + "  วันที่ทำงาน]</b></td></tr>";
                    _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>จำนวนการโทรทั้งหมด  " + ReportMonth + " = " + TotalNewLeads.ToString() + "</b></td></tr>";

                    //Broker Name
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='font-size:14px; background-color:#81BEF7;'><b>&nbsp;</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center' style='font-size:14px; background-color:#81BEF7;'><b>" + BrokerName[j].ToString() + "</b></td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>ยอดรวม</b></td>";
                    _html += "</tr>";

                    //VMI Sales
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>ยอดขายประกันภัย</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + VMISales[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalVMISales.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Avg. Collected Premium
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>เบี้ยประกันโดยเฉลี่ย</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center' style='mso-number-format:\\#\\,\\#\\#0\\.00'>" + CollectedPremium[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100; mso-number-format:\\#\\,\\#\\#0\\.00'><b>" + AvgTotalCollectedPremium.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Sales / Day
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>ขาย / วัน</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + SalesDIVDay[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalSalesDIVDay.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Total Calls
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>ยอดรวมการโทร</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + Calls[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalCalls.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Calls / Day
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>โทร / วัน</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + CallsDIVDay[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalCallsDIVDay.ToString() + "</b></td>";
                    _html += "</tr>";

                    //New Unique Leads Contacted
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>จำนวนลูกค้าที่รอการติดต่อ</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + NewLeads[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalNewLeads.ToString() + "</b></td>";
                    _html += "</tr>";

                    //New Leads / Day
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>จำนวนที่รอติดต่อ / วัน</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + NewLeadsDIVDay[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalNewLeadsDIVDay.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Invalid Numbers
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>ไม่พบหมายเลข</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + InvalidNo[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalInvalidNo.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Contact Rate
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>อัตราการติดต่อ</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + Math.Round(ContactRate[j]).ToString() + "%</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + Math.Round(AvgTotalContactRate).ToString() + "%</b></td>";
                    _html += "</tr>";

                    //Follow-up Calls(Not Unique)
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>การโทรเพื่อติดตาม</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + FollowUpCalls[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalFollowUpCalls.ToString() + "</b></td>";
                    _html += "</tr>";
                }
                else if (ddlLanguage.Text == "2")
                {
                    _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>GG Campaign " + ReportMonth + "</b></td></tr>";
                    _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>[" + NoofWorkingDays.ToString() + "  Working Days]</b></td></tr>";
                    _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>Total New Leads Generated  " + ReportMonth + " = " + TotalNewLeads.ToString() + "</b></td></tr>";

                    //Broker Name
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='font-size:14px; background-color:#81BEF7;'><b>&nbsp;</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center' style='font-size:14px; background-color:#81BEF7;'><b>" + BrokerName[j].ToString() + "</b></td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Total</b></td>";
                    _html += "</tr>";

                    //VMI Sales
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>VMI Sales</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + VMISales[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalVMISales.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Avg. Collected Premium
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Avg. Collected Premium</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center' style='mso-number-format:\\#\\,\\#\\#0\\.00'>" + CollectedPremium[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100; mso-number-format:\\#\\,\\#\\#0\\.00'><b>" + AvgTotalCollectedPremium.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Sales / Day
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Sales / Day</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + SalesDIVDay[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalSalesDIVDay.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Total Calls
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Total Calls</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + Calls[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalCalls.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Calls / Day
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Calls / Day</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + CallsDIVDay[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalCallsDIVDay.ToString() + "</b></td>";
                    _html += "</tr>";

                    //New Unique Leads Contacted
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>New Unique Leads Contacted</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + NewLeads[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalNewLeads.ToString() + "</b></td>";
                    _html += "</tr>";

                    //New Leads / Day
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>New Leads / Day</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='20' align='center'>" + NewLeadsDIVDay[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalNewLeadsDIVDay.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Invalid Numbers
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Invalid Numbers</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + InvalidNo[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalInvalidNo.ToString() + "</b></td>";
                    _html += "</tr>";

                    //Contact Rate
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Contact Rate</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + Math.Round(ContactRate[j]).ToString() + "%</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + Math.Round(AvgTotalContactRate).ToString() + "%</b></td>";
                    _html += "</tr>";

                    //Follow-up Calls(Not Unique)
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Follow-up Calls(Not Unique)</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + FollowUpCalls[j].ToString() + "</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalFollowUpCalls.ToString() + "</b></td>";
                    _html += "</tr>";

                    //ConversionRate Rate
                    _html += "<tr>";
                    _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>IC %</b></td>";
                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<td width='200' align='center'>" + Math.Round(ConversionRate[j]).ToString() + "%</td>";
                    }
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + Math.Round(AvgTotalContactRate).ToString() + "%</b></td>";
                    _html += "</tr>";
                }

                
                _html += "</table>";
                _html += "</body>";
                _html += "</html>";

                #endregion

                lblContent.Text = _html;
                ExportGridView(_fileName, lblContent);
            }
        }
        else
        {
        }
    }

    public void GenerateExcel()
    {
        string _html = "";
        string _fileName = "GGCampaign@" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
        lblContent.Text = "";
        lblError.Text = "";
        if (Validatioin() == true)
        {
            txtReportDate1.Text = "";
            txtReportDate2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000") + CE.Global.General.GetUTCDateTime().Month.ToString("00") + CE.Global.General.GetUTCDateTime().Day.ToString("00");
            txtReportDate1.Text = ddlYear1.Text;
            txtReportDate1.Text += ddlMonth1.Text;
            txtReportDate1.Text += ddlDay1.Text;
            if (ddlReportType.SelectedIndex == 0)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
            }
            if (ddlReportType.SelectedIndex == 1)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
                txtReportDate1.Text += ddlDay1.Text;
            }
            if (ddlReportType.SelectedIndex == 2)
            {
                txtReportDate2.Text = ddlYear2.Text;
                txtReportDate2.Text += ddlMonth2.Text;
                txtReportDate2.Text += ddlDay2.Text;
            }
            Int32 TotalFileCount = 0;
            DataTable _dtSummary = new DataTable();
            Int16 _ReportType = Convert.ToInt16(ddlReportType.Text);
            string _ReportDate1 = txtReportDate1.Text.Trim();
            string _ReportDate2 = txtReportDate2.Text.Trim();

            if (ddlReportType.Text == "0" && _ReportDate1.Length == 6)
            {
                _ReportDate1 += "01";
            }
            Int16 Renewal = Convert.ToInt16(ddlRenewal.Text);

            MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
            string _spName = "[sprocCXCRMReportsBrokersTeleMarketingSelectedItem]";
            SortedList _sPara = new SortedList();

            _sPara.Add("@ReportType", _ReportType);
            _sPara.Add("@ReportDate1", _ReportDate1);
            _sPara.Add("@ReportDate2", _ReportDate2);
            _sPara.Add("@Renewal", Renewal);

            _dtSummary = _db.GetDataTableBySP(_spName, _sPara);
            if (_dtSummary != null && _dtSummary.Rows.Count > 0)
            {
                TotalFileCount += _dtSummary.Rows.Count;

                #region Variable Declaration

                string[] BrokerId = new string[TotalFileCount];
                string[] BrokerName = new string[TotalFileCount];
                Int32[] VMISales = new Int32[TotalFileCount];
                float[] CollectedPremium = new float[TotalFileCount];
                float[] SalesDIVDay = new float[TotalFileCount];
                Int32[] Calls = new Int32[TotalFileCount];
                float[] CallsDIVDay = new float[TotalFileCount];
                Int32[] NewLeads = new Int32[TotalFileCount];
                float[] NewLeadsDIVDay = new float[TotalFileCount];
                Int32[] InvalidNo = new Int32[TotalFileCount];
                float[] ContactRate = new float[TotalFileCount];
                Int32[] FollowUpCalls = new Int32[TotalFileCount];
                float[] ConversionRate = new float[TotalFileCount];

                Int32 TotalVMISales = 0;
                float TotalCollectedPremium = 0.0f;
                float TotalSalesDIVDay = 0.0f;
                Int32 TotalCalls = 0;
                float TotalCallsDIVDay = 0.0f;
                Int32 TotalNewLeads = 0;
                float TotalNewLeadsDIVDay = 0.0f;
                Int32 TotalInvalidNo = 0;
                float TotalContactRate = 0.0f;
                Int32 TotalFollowUpCalls = 0;
                float TotalConversionRate = 0.0f;
                float AvgTotalCollectedPremium = 0.0f;
                float AvgTotalContactRate = 0.0f;
                Int32 NoofWorkingDays = 0;
                float AvgTotalConversionRate = 0.0f;

                #endregion

                #region Value Assign

                for (int i = 0; i < TotalFileCount; i++)
                {
                    BrokerId[i] = _dtSummary.Rows[i]["AgentId"].ToString();
                    BrokerName[i] = _dtSummary.Rows[i]["Name"].ToString();
                    VMISales[i] = Convert.ToInt32(_dtSummary.Rows[i]["VMISales"].ToString());
                    CollectedPremium[i] = Convert.ToSingle(_dtSummary.Rows[i]["CollectedPremium"].ToString());
                    SalesDIVDay[i] = Convert.ToSingle(_dtSummary.Rows[i]["SalesDIVDay"].ToString());
                    Calls[i] = Convert.ToInt32(_dtSummary.Rows[i]["TotalCalls"].ToString());
                    CallsDIVDay[i] = Convert.ToSingle(_dtSummary.Rows[i]["CallsDIVDay"].ToString());
                    NewLeads[i] = Convert.ToInt32(_dtSummary.Rows[i]["NewLeads"].ToString());
                    NewLeadsDIVDay[i] = Convert.ToSingle(_dtSummary.Rows[i]["NewLeadsDIVDay"].ToString());
                    InvalidNo[i] = Convert.ToInt32(_dtSummary.Rows[i]["InvalidNo"].ToString());
                    ContactRate[i] = Convert.ToSingle(_dtSummary.Rows[i]["ContactRate"].ToString());
                    FollowUpCalls[i] = Convert.ToInt32(_dtSummary.Rows[i]["FollowUpCalls"].ToString());
                    NoofWorkingDays = Convert.ToInt32(_dtSummary.Rows[i]["WorkingDays"].ToString());
                    ConversionRate[i] = Convert.ToSingle(_dtSummary.Rows[i]["ConversionRate"].ToString());
                    TotalVMISales += VMISales[i];
                    TotalCollectedPremium += CollectedPremium[i];
                    TotalSalesDIVDay += SalesDIVDay[i];
                    TotalCalls += Calls[i];
                    TotalCallsDIVDay += CallsDIVDay[i];
                    TotalNewLeads += NewLeads[i];
                    TotalNewLeadsDIVDay += NewLeadsDIVDay[i];
                    TotalInvalidNo += InvalidNo[i];
                    TotalContactRate += ContactRate[i];
                    TotalFollowUpCalls += FollowUpCalls[i];
                    TotalConversionRate += ConversionRate[i];

                }

                AvgTotalCollectedPremium = TotalCollectedPremium;
                if (TotalCollectedPremium > 0 && TotalFileCount > 0)
                {
                    AvgTotalCollectedPremium = TotalCollectedPremium / Convert.ToSingle(TotalFileCount);
                }
                AvgTotalContactRate = TotalContactRate;
                if (TotalContactRate > 0 && TotalFileCount > 0)
                {
                    AvgTotalContactRate = TotalContactRate / Convert.ToSingle(TotalFileCount);
                }
                AvgTotalConversionRate = TotalConversionRate;
                if (TotalConversionRate > 0 && TotalFileCount > 0)
                {
                    AvgTotalConversionRate = TotalConversionRate / Convert.ToSingle(TotalFileCount);
                }
                #endregion

                #region Generate Excel

                string ReportMonth = "";
                if (_ReportType == 0)
                {
                    ReportMonth = GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                }
                else if (_ReportType == 1)
                {
                    ReportMonth = ddlDay1.Text + " " + GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                }
                else if (_ReportType == 2)
                {
                    ReportMonth = ddlDay1.Text + " " + GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                    ReportMonth += " - ";
                    ReportMonth += ddlDay2.Text + " " + GetMonth(ddlMonth2.Text).Trim() + " " + ddlYear2.Text;
                }
                _html += "<html>";
                _html += "<head>";
                _html += "<meta http-equiv='content-type' content='text/html; charset=UTF-8'>";
                _html += "</head>";
                _html += "<body>";
                _html += "<table width='100%' cellpadding='1' cellspacing='1' border='1'>";
                if (ddlLanguage.Text == "1")
                {
                    _html += "<tr><td align='left' colspan='13' style='font-size:15px; background-color:#F2F2F2;'><b>กล้วยๆ แคมเปญ " + ReportMonth + "</b></td></tr>";
                    _html += "<tr><td align='left' colspan='13' style='font-size:15px; background-color:#F2F2F2;'><b>[" + NoofWorkingDays.ToString() + "  วันที่ทำงาน]</b></td></tr>";
                    _html += "<tr><td align='left' colspan='13' style='font-size:15px; background-color:#F2F2F2;'><b>จำนวนการโทรทั้งหมด  " + ReportMonth + " = " + TotalNewLeads.ToString() + "</b></td></tr>";


                    _html += "<tr>";
                    _html += "<td width='80' align='center' style='font-size:14px; background-color:#81BEF7;'><b>BrokerId</b></td>";
                    _html += "<td width='250' align='left' style='font-size:14px; background-color:#81BEF7;'><b>Broker Name</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>VMI Sales</b></td>";
                    _html += "<td width='190' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Avg.Collected Premium</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Sales / Day</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Total Calls</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Calls / Day</b></td>";
                    _html += "<td width='150' align='center' style='font-size:14px; background-color:#81BEF7;'><b>New Unique Leads</b></td>";
                    _html += "<td width='150' align='center' style='font-size:14px; background-color:#81BEF7;'><b>New Leads / Day</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Invalid No</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Contact Rate</b></td>";
                    _html += "<td width='150' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Follow-Up Calls</b></td>";
                    _html += "<td width='80' align='center' style='font-size:14px; background-color:#81BEF7;'><b>LC %</b></td>";
                    _html += "</tr>";

                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<tr>";
                        _html += "<td width='80' align='center'>" + BrokerId[j].ToString() + "</td>";
                        _html += "<td width='250' align='left'>" + BrokerName[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + VMISales[j].ToString() + "</td>";
                        _html += "<td width='190' align='center' style='mso-number-format:\\#\\,\\#\\#0\\.00'>" + CollectedPremium[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + SalesDIVDay[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + Calls[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + CallsDIVDay[j].ToString() + "</td>";
                        _html += "<td width='150' align='center'>" + NewLeads[j].ToString() + "</td>";
                        _html += "<td width='150' align='center'>" + NewLeadsDIVDay[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + InvalidNo[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + ContactRate[j].ToString() + "%</td>";
                        _html += "<td width='150' align='center'>" + FollowUpCalls[j].ToString() + "</td>";
                        _html += "<td width='80' align='center'>" + ConversionRate[j].ToString() + "%</td>";
                        _html += "</tr>";
                    }

                    _html += "<tr>";
                    _html += "<td width='80' align='center' style='font-size:17px; background-color:#FFE100;'>Total</td>";
                    _html += "<td width='250' align='center' style='font-size:17px; background-color:#FFE100;'>&nbsp;</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalVMISales.ToString() + "</td>";
                    _html += "<td width='190' align='center' style='font-size:17px; background-color:#FFE100; mso-number-format:\\#\\,\\#\\#0\\.00'>" + AvgTotalCollectedPremium.ToString() + "</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalSalesDIVDay.ToString() + "</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalCalls.ToString() + "</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalCallsDIVDay.ToString() + "</td>";
                    _html += "<td width='150' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalNewLeads.ToString() + "</td>";
                    _html += "<td width='150' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalNewLeadsDIVDay.ToString() + "</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalInvalidNo.ToString() + "</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + AvgTotalContactRate.ToString() + "%</td>";
                    _html += "<td width='150' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalFollowUpCalls.ToString() + "</td>";
                    _html += "<td width='80' align='center' style='font-size:17px; background-color:#FFE100;'>" + Math.Round(AvgTotalConversionRate).ToString() + "%</td>";
                    _html += "</tr>";

                }
                else if (ddlLanguage.Text == "2")
                {
                    _html += "<tr><td align='left' colspan='13' style='font-size:15px; background-color:#F2F2F2;'><b>GG Campaign " + ReportMonth + "</b></td></tr>";
                    _html += "<tr><td align='left' colspan='13' style='font-size:15px; background-color:#F2F2F2;'><b>[" + NoofWorkingDays.ToString() + "  Working Days]</b></td></tr>";
                    _html += "<tr><td align='left' colspan='13' style='font-size:15px; background-color:#F2F2F2;'><b>Total New Leads Generated  " + ReportMonth + " = " + TotalNewLeads.ToString() + "</b></td></tr>";


                    _html += "<tr>";
                    _html += "<td width='80' align='center' style='font-size:14px; background-color:#81BEF7;'><b>BrokerId</b></td>";
                    _html += "<td width='250' align='left' style='font-size:14px; background-color:#81BEF7;'><b>Broker Name</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>VMI Sales</b></td>";
                    _html += "<td width='190' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Avg.Collected Premium</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Sales / Day</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Total Calls</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Calls / Day</b></td>";
                    _html += "<td width='150' align='center' style='font-size:14px; background-color:#81BEF7;'><b>New Unique Leads</b></td>";
                    _html += "<td width='150' align='center' style='font-size:14px; background-color:#81BEF7;'><b>New Leads / Day</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Invalid No</b></td>";
                    _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Contact Rate</b></td>";
                    _html += "<td width='150' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Follow-Up Calls</b></td>";
                    _html += "<td width='80' align='center' style='font-size:14px; background-color:#81BEF7;'><b>LC %</b></td>";
                    _html += "</tr>";

                    for (int j = 0; j < TotalFileCount; j++)
                    {
                        _html += "<tr>";
                        _html += "<td width='80' align='center'>" + BrokerId[j].ToString() + "</td>";
                        _html += "<td width='250' align='left'>" + BrokerName[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + VMISales[j].ToString() + "</td>";
                        _html += "<td width='190' align='center' style='mso-number-format:\\#\\,\\#\\#0\\.00'>" + CollectedPremium[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + SalesDIVDay[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + Calls[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + CallsDIVDay[j].ToString() + "</td>";
                        _html += "<td width='150' align='center'>" + NewLeads[j].ToString() + "</td>";
                        _html += "<td width='150' align='center'>" + NewLeadsDIVDay[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + InvalidNo[j].ToString() + "</td>";
                        _html += "<td width='120' align='center'>" + ContactRate[j].ToString() + "%</td>";
                        _html += "<td width='150' align='center'>" + FollowUpCalls[j].ToString() + "</td>";
                        _html += "<td width='80' align='center'>" + ConversionRate[j].ToString() + "%</td>";
                        _html += "</tr>";
                    }

                    _html += "<tr>";
                    _html += "<td width='80' align='center' style='font-size:17px; background-color:#FFE100;'>Total</td>";
                    _html += "<td width='250' align='center' style='font-size:17px; background-color:#FFE100;'>&nbsp;</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalVMISales.ToString() + "</td>";
                    _html += "<td width='190' align='center' style='font-size:17px; background-color:#FFE100; mso-number-format:\\#\\,\\#\\#0\\.00'>" + AvgTotalCollectedPremium.ToString() + "</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalSalesDIVDay.ToString() + "</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalCalls.ToString() + "</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalCallsDIVDay.ToString() + "</td>";
                    _html += "<td width='150' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalNewLeads.ToString() + "</td>";
                    _html += "<td width='150' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalNewLeadsDIVDay.ToString() + "</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalInvalidNo.ToString() + "</td>";
                    _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'>" + AvgTotalContactRate.ToString() + "%</td>";
                    _html += "<td width='150' align='center' style='font-size:17px; background-color:#FFE100;'>" + TotalFollowUpCalls.ToString() + "</td>";
                    _html += "<td width='80' align='center' style='font-size:17px; background-color:#FFE100;'>" + Math.Round(AvgTotalConversionRate).ToString() + "%</td>";
                    _html += "</tr>";
                }


                _html += "</table>";
                _html += "</body>";
                _html += "</html>";

                #endregion

                lblContent.Text = _html;
                ExportGridView(_fileName, lblContent);
            }
        }
        else
        {
        }
    }

    public void GenerateExcelList()
    {
        string _html = "";
        string _fileName = "GGCampaign@" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
        lblContent.Text = "";
        lblError.Text = "";
        if (Validatioin() == true)
        {

            txtReportDate1.Text = "";
            txtReportDate2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000") + CE.Global.General.GetUTCDateTime().Month.ToString("00") + CE.Global.General.GetUTCDateTime().Day.ToString("00");
            txtReportDate1.Text = ddlYear1.Text;
            txtReportDate1.Text += ddlMonth1.Text;
            txtReportDate1.Text += ddlDay1.Text;
            if (ddlReportType.SelectedIndex == 0)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
            }
            if (ddlReportType.SelectedIndex == 1)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
                txtReportDate1.Text += ddlDay1.Text;
            }
            if (ddlReportType.SelectedIndex == 2)
            {
                txtReportDate2.Text = ddlYear2.Text;
                txtReportDate2.Text += ddlMonth2.Text;
                txtReportDate2.Text += ddlDay2.Text;
            }
            Int32 NoOfDays = 0;
            Int32 TotalFileCount = 0;
            DataSet _dsSummary = new DataSet(); 
            DataTable _dtSummary = new DataTable();
            Int16 _ReportType = Convert.ToInt16(ddlReportType.Text);
            string _ReportDate1 = txtReportDate1.Text.Trim();
            string _ReportDate2 = txtReportDate2.Text.Trim();

            if (ddlReportType.Text == "0" && _ReportDate1.Length == 6)
            {
                _ReportDate1 += "01";
            }
            Int16 Renewal = Convert.ToInt16(ddlRenewal.Text);

            MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
            string _spName = "[sprocCXCRMReportsBrokersTeleMarketingSelectedItemList]";
            SortedList _sPara = new SortedList();

            _sPara.Add("@ReportType", _ReportType);
            _sPara.Add("@ReportDate1", _ReportDate1);
            _sPara.Add("@ReportDate2", _ReportDate2);
            _sPara.Add("@Renewal", Renewal);

            _dsSummary = _db.GetDataSet(_spName, _sPara);

            if (_dsSummary != null && _dsSummary.Tables.Count > 0)
            {
                _html += "<html>";
                _html += "<head>";
                _html += "<meta http-equiv='content-type' content='text/html; charset=UTF-8'>";
                _html += "</head>";
                _html += "<body>";
                _html += "<table width='100%' cellpadding='1' cellspacing='1' border='1'>";

                _dtSummary = _dsSummary.Tables[0];
                if (_dsSummary.Tables.Count > 2 && _dsSummary.Tables[1].Rows.Count > 0 && _dsSummary.Tables[2].Rows.Count > 0)
                {
                    if (!Int32.TryParse(_dsSummary.Tables[1].Rows[0]["NoofDays"].ToString(), out NoOfDays))
                    {
                        NoOfDays = 0;
                    }
                }
                if (NoOfDays > 0)
                {
                    for (int n = 0; n < NoOfDays; n++)
                    {
                        TotalFileCount = 0;
                        string RecordDate = _dsSummary.Tables[2].Rows[n]["ReportDate"].ToString();
                        DataRow[] results = _dtSummary.Select("SDate = '" + RecordDate + "'");
                        TotalFileCount = results.Length;

                        #region Variable Declaration

                        string[] BrokerName = new string[TotalFileCount];
                        Int32[] VMISales = new Int32[TotalFileCount];
                        float[] CollectedPremium = new float[TotalFileCount];
                        float[] SalesDIVDay = new float[TotalFileCount];
                        Int32[] Calls = new Int32[TotalFileCount];
                        float[] CallsDIVDay = new float[TotalFileCount];
                        Int32[] NewLeads = new Int32[TotalFileCount];
                        float[] NewLeadsDIVDay = new float[TotalFileCount];
                        Int32[] InvalidNo = new Int32[TotalFileCount];
                        float[] ContactRate = new float[TotalFileCount];
                        Int32[] FollowUpCalls = new Int32[TotalFileCount];

                        Int32 TotalVMISales = 0;
                        float TotalCollectedPremium = 0.0f;
                        float TotalSalesDIVDay = 0.0f;
                        Int32 TotalCalls = 0;
                        float TotalCallsDIVDay = 0.0f;
                        Int32 TotalNewLeads = 0;
                        float TotalNewLeadsDIVDay = 0.0f;
                        Int32 TotalInvalidNo = 0;
                        float TotalContactRate = 0.0f;
                        Int32 TotalFollowUpCalls = 0;
                        float AvgTotalCollectedPremium = 0.0f;
                        float AvgTotalContactRate = 0.0f;
                        Int32 NoofWorkingDays = 0;

                        #endregion

                        #region Value Assign

                        for (int i = 0; i < TotalFileCount; i++)
                        {
                            BrokerName[i] = results[i]["Name"].ToString();
                            VMISales[i] = Convert.ToInt32(results[i]["VMISales"].ToString());
                            CollectedPremium[i] = Convert.ToSingle(results[i]["CollectedPremium"].ToString());
                            SalesDIVDay[i] = Convert.ToSingle(results[i]["SalesDIVDay"].ToString());
                            Calls[i] = Convert.ToInt32(results[i]["TotalCalls"].ToString());
                            CallsDIVDay[i] = Convert.ToSingle(results[i]["CallsDIVDay"].ToString());
                            NewLeads[i] = Convert.ToInt32(results[i]["NewLeads"].ToString());
                            NewLeadsDIVDay[i] = Convert.ToSingle(results[i]["NewLeadsDIVDay"].ToString());
                            InvalidNo[i] = Convert.ToInt32(results[i]["InvalidNo"].ToString());
                            ContactRate[i] = Convert.ToSingle(results[i]["ContactRate"].ToString());
                            FollowUpCalls[i] = Convert.ToInt32(results[i]["FollowUpCalls"].ToString());
                            NoofWorkingDays = Convert.ToInt32(results[i]["WorkingDays"].ToString());
                            TotalVMISales += VMISales[i];
                            TotalCollectedPremium += CollectedPremium[i];
                            TotalSalesDIVDay += SalesDIVDay[i];
                            TotalCalls += Calls[i];
                            TotalCallsDIVDay += CallsDIVDay[i];
                            TotalNewLeads += NewLeads[i];
                            TotalNewLeadsDIVDay += NewLeadsDIVDay[i];
                            TotalInvalidNo += InvalidNo[i];
                            TotalContactRate += ContactRate[i];
                            TotalFollowUpCalls += FollowUpCalls[i];

                        }

                        AvgTotalCollectedPremium = TotalCollectedPremium;
                        if (TotalCollectedPremium > 0 && TotalFileCount > 0)
                        {
                            AvgTotalCollectedPremium = TotalCollectedPremium / Convert.ToSingle(TotalFileCount);
                        }
                        AvgTotalContactRate = TotalContactRate;
                        if (TotalContactRate > 0 && TotalFileCount > 0)
                        {
                            AvgTotalContactRate = TotalContactRate / Convert.ToSingle(TotalFileCount);
                        }
                        #endregion

                        #region Generate Excel

                        string ReportMonth = "";
                        /*if (_ReportType == 0)
                        {
                            ReportMonth = GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                        }
                        else if (_ReportType == 1)
                        {
                            ReportMonth = ddlDay1.Text + " " + GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                        }
                        else if (_ReportType == 2)
                        {
                            ReportMonth = ddlDay1.Text + " " + GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                            ReportMonth += " - ";
                            ReportMonth += ddlDay2.Text + " " + GetMonth(ddlMonth2.Text).Trim() + " " + ddlYear2.Text;
                        }*/
                        ReportMonth = RecordDate.Substring(6, 2) + " " + GetMonth(RecordDate.Substring(4, 2)).Trim() + " " + RecordDate.Substring(0, 4);

                        if (ddlLanguage.Text == "1")
                        {
                            _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>กล้วยๆ แคมเปญ " + ReportMonth + "</b></td></tr>";
                            _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>[" + NoofWorkingDays.ToString() + "  วันที่ทำงาน]</b></td></tr>";
                            _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>จำนวนการโทรทั้งหมด  " + ReportMonth + " = " + TotalNewLeads.ToString() + "</b></td></tr>";

                            //Broker Name
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='font-size:14px; background-color:#81BEF7;'><b>&nbsp;</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center' style='font-size:14px; background-color:#81BEF7;'><b>" + BrokerName[j].ToString() + "</b></td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>ยอดรวม</b></td>";
                            _html += "</tr>";

                            //VMI Sales
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>ยอดขายประกันภัย</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + VMISales[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalVMISales.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Avg. Collected Premium
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>เบี้ยประกันโดยเฉลี่ย</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center' style='mso-number-format:\\#\\,\\#\\#0\\.00'>" + CollectedPremium[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100; mso-number-format:\\#\\,\\#\\#0\\.00'><b>" + AvgTotalCollectedPremium.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Sales / Day
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>ขาย / วัน</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + SalesDIVDay[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalSalesDIVDay.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Total Calls
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>ยอดรวมการโทร</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + Calls[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalCalls.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Calls / Day
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>โทร / วัน</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + CallsDIVDay[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalCallsDIVDay.ToString() + "</b></td>";
                            _html += "</tr>";

                            //New Unique Leads Contacted
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>จำนวนลูกค้าที่รอการติดต่อ</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + NewLeads[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalNewLeads.ToString() + "</b></td>";
                            _html += "</tr>";

                            //New Leads / Day
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>จำนวนที่รอติดต่อ / วัน</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + NewLeadsDIVDay[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalNewLeadsDIVDay.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Invalid Numbers
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>ไม่พบหมายเลข</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + InvalidNo[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalInvalidNo.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Contact Rate
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>อัตราการติดต่อ</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + Math.Round(ContactRate[j]).ToString() + "%</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + Math.Round(AvgTotalContactRate).ToString() + "%</b></td>";
                            _html += "</tr>";

                            //Follow-up Calls(Not Unique)
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>การโทรเพื่อติดตาม</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + FollowUpCalls[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalFollowUpCalls.ToString() + "</b></td>";
                            _html += "</tr>";
                        }
                        else if (ddlLanguage.Text == "2")
                        {
                            _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>GG Campaign " + ReportMonth + "</b></td></tr>";
                            _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>[" + NoofWorkingDays.ToString() + "  Working Days]</b></td></tr>";
                            _html += "<tr><td align='left' colspan='" + (TotalFileCount + 2) + "' style='font-size:15px; background-color:#F2F2F2;'><b>Total New Leads Generated  " + ReportMonth + " = " + TotalNewLeads.ToString() + "</b></td></tr>";

                            //Broker Name
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='font-size:14px; background-color:#81BEF7;'><b>&nbsp;</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center' style='font-size:14px; background-color:#81BEF7;'><b>" + BrokerName[j].ToString() + "</b></td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:14px; background-color:#81BEF7;'><b>Total</b></td>";
                            _html += "</tr>";

                            //VMI Sales
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>VMI Sales</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + VMISales[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalVMISales.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Avg. Collected Premium
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Avg. Collected Premium</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center' style='mso-number-format:\\#\\,\\#\\#0\\.00'>" + CollectedPremium[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100; mso-number-format:\\#\\,\\#\\#0\\.00'><b>" + AvgTotalCollectedPremium.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Sales / Day
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Sales / Day</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + SalesDIVDay[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalSalesDIVDay.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Total Calls
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Total Calls</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + Calls[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalCalls.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Calls / Day
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Calls / Day</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + CallsDIVDay[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalCallsDIVDay.ToString() + "</b></td>";
                            _html += "</tr>";

                            //New Unique Leads Contacted
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>New Unique Leads Contacted</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + NewLeads[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalNewLeads.ToString() + "</b></td>";
                            _html += "</tr>";

                            //New Leads / Day
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>New Leads / Day</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='20' align='center'>" + NewLeadsDIVDay[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalNewLeadsDIVDay.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Invalid Numbers
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Invalid Numbers</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + InvalidNo[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalInvalidNo.ToString() + "</b></td>";
                            _html += "</tr>";

                            //Contact Rate
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Contact Rate</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + Math.Round(ContactRate[j]).ToString() + "%</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + Math.Round(AvgTotalContactRate).ToString() + "%</b></td>";
                            _html += "</tr>";

                            //Follow-up Calls(Not Unique)
                            _html += "<tr>";
                            _html += "<td width='190' align='left' style='background-color:#fffe97;'><b>Follow-up Calls(Not Unique)</b></td>";
                            for (int j = 0; j < TotalFileCount; j++)
                            {
                                _html += "<td width='200' align='center'>" + FollowUpCalls[j].ToString() + "</td>";
                            }
                            _html += "<td width='120' align='center' style='font-size:17px; background-color:#FFE100;'><b>" + TotalFollowUpCalls.ToString() + "</b></td>";
                            _html += "</tr>";
                        }

                        #endregion

                    }
                }
                _html += "</table>";
                _html += "</body>";
                _html += "</html>";

                lblContent.Text = _html;
                ExportGridView(_fileName, lblContent);
            }
        }
        else
        {
        }
    }

    public void ExportGridView(string fileName, Label lblContent)
    {
        string attachment = "attachment; filename=" + fileName + ".xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/ms-excel";
        Response.Write(lblContent.Text);
        Response.End();
    }

    private void CheckPageAccess()
    {
        Int64 PageAccess = General.GetPageAccess(Convert.ToInt64(Session[General.UserId].ToString()), PageCode);
        if (PageAccess == 0)
        {
            Response.Redirect("~/UserMessage.aspx?menu=0&mt=UM&ec=101");
        }
        else
        {
            EnablePageAccess(PageAccess);
        }
    }

    private void EnablePageAccess(Int64 PageAccess)
    {
        if (PageAccess == 100)
        {
            btnDownLoad.Enabled = false;
        }
        else if (PageAccess == 200)
        {
            btnDownLoad.Enabled = true;
        }
    }

}
